package indi.wshape1.takeawaydeliveryinfo.dao.impl;

import indi.wshape1.takeawaydeliveryinfo.dao.SenderDAO;
import indi.wshape1.takeawaydeliveryinfo.domain.Sender;

import java.sql.SQLException;
import java.util.List;

/**
 * @author Wshape1
 * @create 2022-11-11 11:43
 */

public class SenderDAOImpl extends BaseDAOImpl<Sender> implements SenderDAO {

    @Override
    public Sender getByPhone(String phone) {
        List<Sender> senders = executeQuery("SELECT `se_name`, `phone`, `password`, `group`, `block` FROM `sender` WHERE `phone` = ? LIMIT 1", phone);
        if (senders.size() > 0)
            return senders.get(0);
        return null;
    }

    @Override
    public void add(Sender sender) {
        try {
            executeUpdate("INSERT INTO `sender`(`se_name`, `phone`, `password`, `group`, `block`) VALUES (?, ?, ?, ?, ?)",
                    sender.getName(),
                    sender.getPhone(),
                    sender.getPassword(),
                    sender.getGroup(),
                    sender.getBlock()
            );
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public void delByPhone(String phone) throws SQLException {
        executeUpdate("DELETE FROM `sender` WHERE `phone` = ? LIMIT 1", phone);
    }

    @Override
    public void update(Sender sender, String oldPhone) throws SQLException {
        executeUpdate("UPDATE `sender` SET `se_name` = ?, `phone` = ?, `password` = ?, `group` = ?, `block` = ? " +
                        "WHERE `phone` = ? LIMIT 1",
                sender.getName(),
                sender.getPhone(),
                sender.getPassword(),
                sender.getGroup(),
                sender.getBlock(),
                oldPhone
        );
    }

    @Override
    public List<Sender> getList() {
        return executeQuery("SELECT `se_name`, `phone`, `password`, `group`, `block` FROM `sender`");
    }

    @Override
    public List<Sender> getListByPage(int page) {
        return executeQuery("SELECT `se_name`, `phone`, `password`, `group`, `block` FROM `sender` LIMIT ?, ?",
                (page - 1) * countPerPage,
                countPerPage);
    }

    @Override
    public List<Sender> getListByKeywordAndPage(String keyword, int page) {
        return executeQuery("SELECT `se_name`, `phone`, `password`, `group`, `block` FROM `sender` WHERE `se_name` LIKE ? or `phone` LIKE ? or `group` LIKE ? or `block` LIKE ? LIMIT ?, ?",
                "%" + keyword + "%",
                "%" + keyword + "%",
                "%" + keyword + "%",
                "%" + keyword + "%",
                (page - 1) * countPerPage,
                countPerPage);
    }

    @Override
    public List<Sender> getListByKeyword(String keyword) {
        return executeQuery("SELECT `se_name`, `phone`, `password`, `group`, `block` FROM `sender` WHERE `se_name` LIKE ? or `phone` LIKE ? or `group` LIKE ? or `block` LIKE ?",
                "%" + keyword + "%",
                "%" + keyword + "%",
                "%" + keyword + "%",
                "%" + keyword + "%");
    }

    @Override
    public int getCount() {
        return ((Long) executeComplexQuery("SELECT COUNT(*) FROM `sender`")[0]).intValue();
    }

    @Override
    public int getCountByKeyword(String keyword) {
        return ((Long) executeComplexQuery(
                "SELECT COUNT(*) FROM `sender` WHERE `se_name` LIKE ? or `phone` LIKE ? or `group` LIKE ? or `block` LIKE ?",
                "%" + keyword + "%",
                "%" + keyword + "%",
                "%" + keyword + "%",
                "%" + keyword + "%"
        )[0]).intValue();
    }

    @Override
    public List<Sender> getListByGroup(String group) {
        return executeQuery("SELECT `se_name`, `phone`, `password`, `group`, `block` FROM `sender` WHERE `group` = ?", group);
    }

    @Override
    public List<Sender> getListByBlock(String block) {
        return executeQuery("SELECT `se_name`, `phone`, `password`, `group`, `block` FROM `sender` WHERE `block` = ?", block);
    }

    @Override
    public List<Sender> getListByGroupAndBlock(String group, String block) {
        return executeQuery("SELECT `se_name`, `phone`, `password`, `group`, `block` FROM `sender` WHERE `group` = ?, `block` = ?", group, block);
    }

    @Override
    public boolean batchUpdateBy(String limitAttr, String[] attrs, String... valAndLastLimitVal) throws SQLException {
        if (attrs.length + 1 == valAndLastLimitVal.length && attrs.length > 0) {
            StringBuilder stringBuffer = new StringBuilder("UPDATE `sender` SET ");
            stringBuffer.append("`").append(attrs[0]).append("`");
            stringBuffer.append(" = ?");
            for (int i = 1; i < attrs.length; i++) {
                stringBuffer.append(", `");
                stringBuffer.append(attrs[i]);
                stringBuffer.append("` = ?");
            }
            stringBuffer.append(" WHERE `");
            stringBuffer.append(limitAttr);
            stringBuffer.append("` = ?");
            executeUpdate(stringBuffer.toString(), valAndLastLimitVal);
            return true;
        }
        return false;
    }

    @Override
    public List<Sender> getListByBlockByKeywordAndPage(String block, String keyword, int page) {
        return executeQuery("call getSenderListByBlockByKeywordAndPage('" + block + "', '%" +
                keyword +
                "%', " + (page - 1) * countPerPage + ", " + countPerPage + ")"
        );
    }

    @Override
    public List<Sender> getListByGroupAndBlockByKeywordAndPage(String group, String block, String keyword, int page) {
        return executeQuery("call getSenderListByGroupAndBlockByKeywordAndPage('" + group + "', '" + block + "', '%" +
                keyword +
                "%', " + (page - 1) * countPerPage + ", " + countPerPage + ")"
        );

    }

    @Override
    public int getCountByBlock(String block) {
        return ((Long) executeComplexQuery(
                "SELECT COUNT(*) FROM `sender` WHERE `block` = ?", block
        )[0]).intValue();
    }

}
